package memo.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;

import memo.pojo.ListMemo;
import models.util.DbUtils;
import models.util.MySqlDataAccessHelper;


public class ListMemoDAO {

	public ArrayList<ListMemo> getAllMemoEmp(String emp, String dbname){
		ArrayList<ListMemo> temp = new ArrayList<ListMemo>();
		PreparedStatement pstmt = null;
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		Savepoint sp = null;
		ResultSet rs = null;
		ListMemo object = null;
		try{
			helper.open(dbname);
			helper.getConnection().setAutoCommit(false);
			sp = helper.getConnection().setSavepoint("savePoint");
			String query = "select * from list_memo b where b.IDEmployee=? order by b.CreatedDate desc";
			pstmt = helper.getConnection().prepareStatement(query);
			pstmt.setString(1, emp);
			rs = pstmt.executeQuery();
			while(rs.next()){
				object = new ListMemo();
				object.setId(rs.getInt("IDMemo"));
				object.setTitle(rs.getString("Title"));
				object.setContent(rs.getString("Content"));
				object.setCreatedDate(rs.getTimestamp("CreatedDate"));
				object.setStatus(rs.getInt("Status"));
				object.getEmployee().setIdemployee(rs.getString("IDEmployee"));
				temp.add(object);
			}
			helper.getConnection().commit();
		}catch(Exception ex){
			ex.printStackTrace(System.out);
			try {
				helper.getConnection().rollback(sp);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
			try {
				if(helper.getConnection() != null){
					DbUtils.close(helper.getConnection());
				}
				if (pstmt != null) {
					DbUtils.close(pstmt);   	
	            }
				if(rs != null){
					DbUtils.close(rs);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
		return temp;
	}
	
	
	public boolean createMemo(ListMemo object, String dbname){
		int row = 0;
		PreparedStatement pstmt = null;
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		Savepoint sp = null;
		try{
			helper.open(dbname);
			helper.getConnection().setAutoCommit(false);
			sp = helper.getConnection().setSavepoint("savePoint");
			String query = "INSERT INTO `list_memo` (`Title`, `Content`, `IDEmployee`, `CreatedDate`, `Status`) VALUES (?,?,?,NOW(),1)";
			pstmt = helper.getConnection().prepareStatement(query);
			pstmt.setString(1, object.getTitle());
			pstmt.setString(2, object.getContent());
			pstmt.setString(3, object.getEmployee().getIdemployee());
			row = pstmt.executeUpdate();
			helper.getConnection().commit();
		}catch(Exception ex){
			ex.printStackTrace(System.out);
			try {
				helper.getConnection().rollback(sp);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
			try {
				if(helper.getConnection() != null){
					DbUtils.close(helper.getConnection());
				}
				if (pstmt != null) {
					DbUtils.close(pstmt);   	
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
		return row > 0;
	}
	
	public boolean updateMemo(ListMemo object, String dbname){
		int row = 0;
		PreparedStatement pstmt = null;
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		Savepoint sp = null;
		try{
			helper.open(dbname);
			helper.getConnection().setAutoCommit(false);
			sp = helper.getConnection().setSavepoint("savePoint");
			String query = "update list_memo b set b.Title = ? , b.Content = ?, CreatedDate = NOW() where b.IDMemo = ? ";
			pstmt = helper.getConnection().prepareStatement(query);
			pstmt.setString(1, object.getTitle());
			pstmt.setString(2, object.getContent());
			pstmt.setInt(3, object.getId());
			row = pstmt.executeUpdate();
			helper.getConnection().commit();
		}catch(Exception ex){
			ex.printStackTrace(System.out);
			try {
				helper.getConnection().rollback(sp);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
			try {
				if(helper.getConnection() != null){
					DbUtils.close(helper.getConnection());
				}
				if (pstmt != null) {
					DbUtils.close(pstmt);   	
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
		return row > 0;
	}
	
	public boolean deleteMemo(int id, String dbname){
		int row = 0;
		PreparedStatement pstmt = null;
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		Savepoint sp = null;
		try{
			helper.open(dbname);
			helper.getConnection().setAutoCommit(false);
			sp = helper.getConnection().setSavepoint("savePoint");
			String query = "delete from list_memo where IDMemo = ? ";
			pstmt = helper.getConnection().prepareStatement(query);
			pstmt.setInt(1, id);
			row = pstmt.executeUpdate();
			helper.getConnection().commit();
		}catch(Exception ex){
			ex.printStackTrace(System.out);
			try {
				helper.getConnection().rollback(sp);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
			try {
				if(helper.getConnection() != null){
					DbUtils.close(helper.getConnection());
				}
				if (pstmt != null) {
					DbUtils.close(pstmt);   	
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
		return row > 0;
	}
	
	public boolean deleteMultiMemo(String id, String dbname){
		int row = 0;
		PreparedStatement pstmt = null;
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		Savepoint sp = null;
		try{
			helper.open(dbname);
			helper.getConnection().setAutoCommit(false);
			sp = helper.getConnection().setSavepoint("savePoint");
			String query = "delete from list_memo where IDMemo IN( ? )";
			pstmt = helper.getConnection().prepareStatement(query);
			pstmt.setString(1, id);
			row = pstmt.executeUpdate();
			helper.getConnection().commit();
		}catch(Exception ex){
			ex.printStackTrace(System.out);
			try {
				helper.getConnection().rollback(sp);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
			try {
				if(helper.getConnection() != null){
					DbUtils.close(helper.getConnection());
				}
				if (pstmt != null) {
					DbUtils.close(pstmt);   	
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
		return row > 0;
	}
}
